using System;
using System.Collections;
using System.Data;
using BO = PHSRAG.Insight21.BusinessObjects;
using Utility = PHSRAG.Utility;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using DataAccess = PHSRAG.Utility.DataAccess;
using System.Data.SqlClient;
using PHSRAG.Insight21.Agreements;
using PHSRAG.Insight21.CDS;
using System.Web;
using System.Text;


namespace PHSRAG.Insight21.Humans
{
    /// <summary>
    /// Summary description for Application.
    /// </summary>
    public class Application : ProtocolProcess
    {

        #region COnstants
        public const string Drug = "Drug";
        public const string Device = "Device";
        public const string EnrollmentSite = "sEnrollmentSite";
        public const string SubjectType = "sSubjectType";
        public const string StudyType = "sStudyType";
        public const string RadiationCategory = "sRadiationCategory";
        public const string HumanMaterialSources = "sHumanSource";
        public const string MedicalRecordsSource = "sMedicalSource";
        public const string SpecialConsideration = "sSpecialConsideration";
        #endregion

        public Application(ICachePolicy cachePolicy)
            : base(cachePolicy)
        {
            // set the type.
            Type = Constants.ProtocolProcessType.InitialReview;
        }

        #region Constructor
        /// <summary>
        /// Constructor for Application which takes a data row.
        /// </summary>
        /// <param name="cachePolicy"></param>
        /// <param name="dataRow"></param>
        public Application(ICachePolicy cachePolicy, DataRow dataRow)
            : base(cachePolicy, dataRow)
        {
            // set the type.
            Type = Constants.ProtocolProcessType.InitialReview;
        }
        #endregion

        #region Overriden Methods
        /// <summary>
        /// Adds a new protocol document to the ProtocolDocuments collection. Sets the IsCurrent flag to true.
        /// </summary>
        /// <param name="protocolId">Protocol Id</param>
        /// <param name="title">Title of the attachment</param>
        /// <param name="type">Type of the attachment</param>
        /// <param name="isHardCopy">Indicates whether or not the attachment is a hardcopy or not</param>
        /// <param name="protocolProcessId">Protocol Process Id</param>
        /// <returns>The new ProtocolDocument object added to the container</returns>
        public override ProtocolDocument AddProtocolDocument(int protocolId, string title, string type, string refMeaning, bool isHardCopy, int protocolProcessId)
        {
            ProtocolDocument newProtocolDocument = base.AddProtocolDocument(protocolId, title, type, refMeaning, isHardCopy, protocolProcessId);
            newProtocolDocument.IsCurrent = true;
            return newProtocolDocument;
        }


        /// <summary>
        /// This overridden method sets Added Protocol Process ID, Protocol ID and sets current flag to true.
        /// StudyStaff object gets saved after changes.		
        /// </summary>
        /// <param name="StaffPerson">StaffPerson Object</param>
        public override void AddStudyStaff(StaffPerson staffPerson)
        {
            try
            {
                staffPerson.AddedProtocolProcessId = Id;
                staffPerson.ProtocolId = ProtocolId;
                staffPerson.IsCurrent = true;
                StudyStaff.Add(staffPerson);
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog(
                    "Failed to add study staff in Application", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Failed to add study staff in Application", ex);
            }

        }

        /// <summary>
        /// This overridden method sets Removed Protocol Process ID, and sets current flag to false.
        /// StudyStaff object gets saved after changes.
        /// </summary>
        /// <param name="connectionString">connection to the DB.</param>
        /// <param name="StaffPerson">StaffPerson Object</param>
        public override void RemoveStudyStaff(string connectionString, StaffPerson staffPerson)
        {
            try
            {
                StudyStaff.Remove(staffPerson);
                staffPerson.Delete(connectionString);
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog(
                    "Failed to remove study staff in Application", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Failed to remove study staff in Application", ex);
            }
        }

        /// <summary>
        /// For the application, you have to set the Protocol's status to Locked
        /// and Unlocked as well, since it's the first one. Note that the Lock method
        /// will save the ProtocolProcess and Protocol changes to the database.
        /// </summary>
        /// <param name="connectionString">database connection string</param>
        /// <param name="cachePolicy">prevalent cachepolicy</param>
        /// <param name="parentProtocol">OPTIONAL Parent protocol to be used in setting 
        /// locked status if present.</param>
        public override void Lock(string connectionString, ICachePolicy cachePolicy, Protocol parentProtocol)
        {
            if (parentProtocol == null)
            {
                parentProtocol = new Protocol(cachePolicy);
                parentProtocol.Load(connectionString, ProtocolId);
            }

            parentProtocol.IsLocked = true;

            parentProtocol.Save(connectionString);

            base.Lock(connectionString, cachePolicy, parentProtocol);
        }


        /// <summary>
        /// For the application, you have to set the Protocol's status to Locked
        /// and Unlocked as well, since it's the first one. Note that the Lock method
        /// will save the ProtocolProcess and Protocol changes to the database.
        /// </summary>
        /// <param name="connectionString">database connection string</param>
        /// <param name="cachePolicy">prevalent cachepolicy</param>
        /// <param name="transaction">OPTIONAL Sql Transaction which will be used to save if present.</param>
        public override void Unlock(string connectionString, ICachePolicy cachePolicy, Protocol parentProtocol)
        {
            if (parentProtocol == null)
            {
                parentProtocol = new Protocol(cachePolicy);
                parentProtocol.Load(connectionString, ProtocolId);
            }

            parentProtocol.IsLocked = false;

            parentProtocol.Save(connectionString);

            base.Unlock(connectionString, cachePolicy, parentProtocol);
        }



        /// <summary>
        /// For the application, if it's the first time, move over the Protocol as 
        /// well as the Initial Review, and set the Protocol ID.
        /// </summary>
        /// <param name="cachePolicy">passed in relevant cachepolicy</param>
        /// <param name="insightConnectionString">Insight DB connectionstring</param>
        /// <param name="rexConnectionString">Rex DB's connectionString</param>
        public override void Submit(ICachePolicy cachePolicy, string insightConnectionString,
            string rexConnectionString, string executingAssemblyPath, RexParams rexParameters,
                    bool IsRelocked)
        {
            if (rexParameters == null)
                rexParameters = new RexParams();

            // get all the intrinsics and run teh Submission script here. 
            // Also, add in the rest of the Rex fields, checking with Irina 
            // for any new stored procedure changes.  
            #region Runscript
            Protocol parentProt = new Protocol(cachePolicy);
            parentProt.Load(insightConnectionString, this.ProtocolId);


            string submissionScriptType;
            if (parentProt.ModuleType == Humans.Constants.ModuleType.Humans)
                submissionScriptType = (string)ScriptEngine.Constants.ScriptType.HumansSubmission;
            else
            {
                submissionScriptType = (string)ScriptEngine.Constants.ScriptType.AnimalSubmissionInitialReveiew;
                rexParameters = (AnimalsRexParams)rexParameters;
            }

            ArrayList scripts = ScriptEngine.Script.GetScriptsByType(insightConnectionString,
                submissionScriptType, cachePolicy);

            ScriptEngine.Script submissionScript = null;

            if (scripts.Count > 0)
                submissionScript = (ScriptEngine.Script)scripts[0];

            if (submissionScript != null)
            {
                using (ScriptEngine.ScriptEngineHost scriptHost =
               new PHSRAG.Insight21.ScriptEngine.ScriptEngineHost(
                          ScriptEngine.Constants.RootMoniker.SubmissionScriptMoniker,
                          ScriptEngine.Constants.RootNamespaces.SubmissionScript,
                          true))
                {
                    ArrayList refs = ScriptEngine.ScriptReference.GetAllScriptReferences(
                        insightConnectionString);

                    for (int i = 0; i < refs.Count; ++i)
                    {
                        ScriptEngine.ScriptReference reference = (ScriptEngine.ScriptReference)refs[i];
                        scriptHost.AddDLLReference(string.Format("{0}\\{1}", executingAssemblyPath, reference.Name));
                        //scriptHost.AddDLLReference ( reference.Name );	
                    }

                    // add the connection string
                    scriptHost.AddIntrinsicObject(ScriptEngine.Constants.Instrinsics.ConnectionString,
                        insightConnectionString);

                    // add the Process
                    scriptHost.AddIntrinsicObject(ScriptEngine.Constants.Instrinsics.ProtocolProcss,
                        this);

                    ProtocolVersion currentVersion = GetLatestProtocolVersion(insightConnectionString,
                        false, cachePolicy) as ProtocolVersion;
                    if (currentVersion == null)
                    {
                        throw new Exception("No protocol version found");
                    }
                    scriptHost.AddIntrinsicObject(ScriptEngine.Constants.Instrinsics.ProtocolVersion, currentVersion);


                    // add the RexParams
                    scriptHost.AddIntrinsicObject(ScriptEngine.Constants.Instrinsics.RexParams, rexParameters);

                    // add the caching policy
                    scriptHost.AddIntrinsicObject(ScriptEngine.Constants.Instrinsics.CachePolicy, cachePolicy);

                    // compile and run the script.
                    if (scriptHost.Compile(submissionScript))
                        scriptHost.RunScript(submissionScript,
                            ScriptEngine.Constants.AssemblyType.SubmissionScript,
                            ScriptEngine.Constants.MethodsToRun.Main);
                }
            }
            #endregion

            // move over the Protocol to Rex.
            //Protocol parentProt = new Protocol( cachePolicy );
            //parentProt.Load ( insightConnectionString, this.ProtocolId );

            // pre-load the Study staff here to not cause DB Blocks.
            int count = StudyStaff.Count;

            SqlConnection rexConn = new SqlConnection(rexConnectionString);
            SqlConnection insightConn = new SqlConnection(insightConnectionString);

            SqlTransaction rexTrans = null;
            SqlTransaction insightTrans = null;

            #region Humans submission
            if (parentProt.ModuleType == Humans.Constants.ModuleType.Humans)
            {

                try
                {
                    rexConn.Open();
                    rexTrans = rexConn.BeginTransaction();

                    insightConn.Open();
                    insightTrans = insightConn.BeginTransaction();

                    bool insertStatusRecords = false;
                    bool newProtocol = false;

                    if (parentProt.ProtocolNumber == "" || parentProt.ProtocolNumber == string.Empty)
                    {
                        // first get the new Foldernumber.
                        DataSet rexResults = Utility.DataAccess.GetDataSet(rexConnectionString,
                            "spIRBProtocolKeyGetNext",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@sKeyName", SqlDbType.VarChar, "sProtocolNumber"));

                        string protocolNumber = (string)rexResults.Tables[0].Rows[0]["sKeyValue"];

                        parentProt.ProtocolNumber =
                            protocolNumber.Substring(0, 4) + "P" + protocolNumber.Substring(4, 6);

                        insertStatusRecords = true;
                        newProtocol = true;
                    }

                    CDS.Person PI = new CDS.Person(cachePolicy);
                    PI.Load(insightConnectionString, parentProt.PIPersonId);

                    // check the PI has a Recom ID since Rex requires it.
                    if (PI != null && PI.RexRecomId == 0)
                    {
                        // insert the PI
                        PI.CreateRecomRecord(insightConnectionString, rexConnectionString);
                    }

                    CDS.Person hroAdmin = new CDS.Person(cachePolicy);
                    hroAdmin.Load(insightConnectionString, parentProt.AdministratorId);
                    if (hroAdmin != null && hroAdmin.RexRecomId == 0)
                    {
                        //insert
                        hroAdmin.CreateRecomRecord(insightConnectionString, rexConnectionString);
                    }

                    CDS.Organization PIOrg = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrg.Load(insightConnectionString, PI.OrganizationId);

                    // create Institution, Department and Unit Orgs for the PI's Org.
                    CDS.Organization PIOrgInstitution = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgInstitution.Load(insightConnectionString, PIOrg.InstitutionId);

                    CDS.Organization PIOrgDept = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgDept.Load(insightConnectionString, PIOrg.DepartmentId);
                    CDS.Organization PIOrgUnit = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgUnit.Load(insightConnectionString, PIOrg.UnitId);



                    string unitName;
                    if (PIOrgUnit == null || PIOrgUnit.Name == null || PIOrgUnit.Name == string.Empty)
                        unitName = "(None)";
                    else
                        unitName = PIOrgUnit.Name + " " + PIOrgUnit.ChiefCode;

                    Sponsor sponsor = new Sponsor(cachePolicy);
                    try
                    {
                        sponsor.Load(insightConnectionString, parentProt.SponsorId);
                    }
                    catch { }

                    int currentDocumentNumber = 1;

                    // if the ID isn't zero, look it up to see if
                    // what we have is the current attachmentTitle # (in case it's
                    // been cloned in Rex bc of Amendment or Correction)
                    if (parentProt.RexProtocolId != 0)
                    {
                        currentDocumentNumber = base.GetCurentRexDocumentNumber(rexConnectionString, rexTrans, parentProt.ProtocolNumber, Constants.ModuleType.Humans);
                        parentProt.RexProtocolId = base.GetCurrentRexProtocolId(rexConnectionString, rexTrans, parentProt.ProtocolNumber, Constants.ModuleType.Humans);
                       
                    }

                    string protocolType = (parentProt.ProtocolTypeCode == Humans.Constants.NewProtocolType.NewApplication) ? Humans.Constants.RexProtocolType.StandardApplication : Humans.Constants.RexProtocolType.EmergencyApplication;
                    if (rexParameters.ProtocolTypeOverride != string.Empty)
                        protocolType = rexParameters.ProtocolTypeOverride;

                    int rexProtId;

                    // default these to Now. If it's not a new protocol, we'll look them up and 
                    // use the existing ones. Otherwise, we'll keep them as now for new ones.
                    DateTime dateReceived = DateTime.Now;
                    DateTime dateIssued = DateTime.Now;
                    DateTime dtApproval = DateTime.MinValue;
                    DateTime dtExpiration = DateTime.MinValue;
                    int nApprovalPeriod = 1;
                    DateTime dtTermination = DateTime.MinValue;
                    DateTime dtCompletion = DateTime.MinValue;
                    DateTime dtActivation = DateTime.MinValue;
                    DateTime dtClosure = DateTime.MinValue;
                    string sRelatedFolderNumber = "";
                    string sLegacyIdentifier = string.Empty;
                    string sRiskType = "";
                    string sOverallStatus = parentProt.OverallStatus;
                    string sStudyPhase = "";

                    if (!newProtocol)
                    {

                        // get the existing date Received and Date Issued, and pass them in so as to not
                        // overwrite what Rex has. CDC: This will go away when these properties are added
                        // directly to the protocol in 2.1.4 (hopefully)....
                        DataSet protocolInfo = Utility.DataAccess.GetDataSet(
                            rexConn, rexTrans,
                            "spIRBGeneralInfoGet",
                            Utility.DataAccess.BuildSqlParameterArray("@nIRBProtocolID", SqlDbType.Int, parentProt.RexProtocolId));

                        
                        object o = protocolInfo.Tables[0].Rows[0]["dtReceived"];
                        dateReceived = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtIssue"];
                        dateIssued = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtApproval"];
                        dtApproval = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtExpiration"];
                        dtExpiration = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtTermination"];
                        dtTermination = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtCompletion"];
                        dtCompletion = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtActivation"];
                        dtActivation = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtClosure"];
                        dtClosure = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["sRelatedFolderNumber"];
                        sRelatedFolderNumber = (o == DBNull.Value) ? string.Empty : (string)o;

                        o = protocolInfo.Tables[0].Rows[0]["sInstitutionID"];
                        sLegacyIdentifier = (o == DBNull.Value) ? string.Empty : (string)o;

                        o = protocolInfo.Tables[0].Rows[0]["nApprovalPeriod"];
                        nApprovalPeriod = (o == DBNull.Value) ? 0 : (int)o;

                        o = protocolInfo.Tables[0].Rows[0]["sOverallStatusIRB"];
                        if (o != DBNull.Value)
                        {
                            if ((string)o != sOverallStatus)
                                sOverallStatus = (string)o;
                        }

                        o = protocolInfo.Tables[0].Rows[0]["sRiskType"];
                        sRiskType = (o == DBNull.Value) ? string.Empty : (string)o;

                        o = protocolInfo.Tables[0].Rows[0]["sStudyPhase"];
                        sStudyPhase = (o == DBNull.Value) ? string.Empty : (string)o;

                        if (rexParameters.SubjectCount == 0)
                        {
                            o = protocolInfo.Tables[0].Rows[0]["nSubjects"];
                            rexParameters.SubjectCount = (o == DBNull.Value) ? 0 : (int)o;
                        }

                        if (rexParameters.GlobalEnrollment == 0)
                        {
                            o = protocolInfo.Tables[0].Rows[0]["nGlobalEnrollment"];
                            rexParameters.GlobalEnrollment = (o == DBNull.Value) ? 0 : (int)o;
                        }
                    }

                    rexProtId = Convert.ToInt32(Utility.DataAccess.ExecuteScalar(rexConn, rexTrans,
                        "spIRBGeneralInfoUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, parentProt.RexProtocolId,
                        "@sProtocolTitle", SqlDbType.VarChar, parentProt.Title,
                        "@sProtocolShortTitle", SqlDbType.VarChar, parentProt.Title,
                        "@nPIID", SqlDbType.Int, PI.RexRecomId,
                        "@nSponsorID", SqlDbType.Int, (sponsor.RexSponsorId == 0) ? (object)System.DBNull.Value : (object)sponsor.RexSponsorId,
                        "@sVersionName", SqlDbType.VarChar, parentProt.Version,
                        "@dtIssue", SqlDbType.DateTime, dateIssued,
                        "@dtReceived", SqlDbType.DateTime, dateReceived,
                        "@nAdministratorID", SqlDbType.Int, hroAdmin.RexRecomId,
                        "@sPIInstitution", SqlDbType.VarChar, PIOrgInstitution.Name + " " + PIOrgInstitution.ChiefCode,
                        "@sPIDept", SqlDbType.VarChar, PIOrgDept.Name + " " + PIOrgDept.ChiefCode,
                        "@sPIUnit", SqlDbType.VarChar, unitName,
                        "@sPILab", SqlDbType.VarChar, "",
                        "@sProtocolType", SqlDbType.VarChar, protocolType,
                        "@sStudyPhase", SqlDbType.VarChar, sStudyPhase,
                        "@sMPANumber", SqlDbType.VarChar, PIOrgInstitution.FWANumber,
                        "@bStocking", SqlDbType.Bit, 0,
                        "@bOnline", SqlDbType.Bit, 0,
                        "@sFolderNumber", SqlDbType.VarChar, parentProt.ProtocolNumber,
                        "@sResponsibleInstitutionIRB", SqlDbType.VarChar, parentProt.ResponsibleIRB,
                        "@nSubjects", SqlDbType.Int, rexParameters.SubjectCount,
                        "@nGlobalEnrollment", SqlDbType.Int, rexParameters.GlobalEnrollment,
                        "@sRiskType", SqlDbType.VarChar, sRiskType,
                        "@dtApproval", SqlDbType.DateTime, (dtApproval == DateTime.MinValue) ? (object)DBNull.Value : dtApproval,
                        "@dtExpiration", SqlDbType.DateTime, (dtExpiration == DateTime.MinValue) ? (object)DBNull.Value : dtExpiration,
                        "@sOverallStatusIRB", SqlDbType.VarChar, sOverallStatus,
                        "@nDocumentNumber", SqlDbType.Int, currentDocumentNumber,
                        "@sLegacyIdentifier", SqlDbType.VarChar, (sLegacyIdentifier == string.Empty) ? (object)DBNull.Value : sLegacyIdentifier,
                        "@nApprovalPeriod", SqlDbType.Int, nApprovalPeriod,
                        "@dtActivation", SqlDbType.DateTime, (dtActivation == DateTime.MinValue) ? (object)DBNull.Value : dtActivation,
                        "@dtCompletion", SqlDbType.DateTime, (dtCompletion == DateTime.MinValue) ? (object)DBNull.Value : dtCompletion,
                        "@dtClosure", SqlDbType.DateTime, (dtClosure == DateTime.MinValue) ? (object)DBNull.Value : dtClosure,
                        "@dtTermination", SqlDbType.DateTime, (dtTermination == DateTime.MinValue) ? (object)DBNull.Value : dtTermination,
                        "@bRexOwned", SqlDbType.Bit, 0,
                        "@PrtProtocolID", SqlDbType.Int, 0,
                        "@sRelatedFolderNumber", SqlDbType.VarChar, sRelatedFolderNumber,
                        "@EIRBProtocolId", SqlDbType.Int, parentProt.Id,

                        "@EIRBCreated", SqlDbType.Bit, 1)));

                    // only update these if it's WIP (to prevent changing the status if the protocol is unlocked
                    // then locked again.
                    if (parentProt.OverallStatusId == (int)Humans.Constants.ProtocolOverallStatus.WorkInProgress)
                    {
                        parentProt.OverallStatusId = (int)Humans.Constants.ProtocolOverallStatus.Pending;
                        Status = Humans.Constants.ProtocolProcessStatus.Pending;
                        parentProt.SubmissionDate = DateTime.Now;
                        this.ReceivedDate = DateTime.Now;
                    }

                    if (parentProt.OverallStatusId == (int)Humans.Constants.ProtocolOverallStatus.Submitted)
                    {
                        this.ReceivedDate = DateTime.Now;
                    }

                    parentProt.RexProtocolId = rexProtId;


                    foreach (StaffPerson person in StudyStaff)
                    {
                        person.ProtocolNumber = parentProt.ProtocolNumber;
                    }

                    foreach (NonStaffPerson person in NonStudyStaff)
                    {
                        person.ProtocolNumber = parentProt.ProtocolNumber;
                    }

                    parentProt.Save(insightTrans);
                    Save(insightTrans);

                    /// Update the enrollment.
                    Utility.DataAccess.ExecuteScalar(rexConn, rexTrans,
                        "spIRBSubjectsUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, parentProt.RexProtocolId,
                        "@nSubjects", SqlDbType.Int, rexParameters.SubjectCount,
                        "@nGlobalEnrollment", SqlDbType.Int, rexParameters.GlobalEnrollment,
                        "@sSubjectPopulation", SqlDbType.VarChar, string.Empty));

                    // spIRBStatusUpdate
                    int irbStatusId = Convert.ToInt32(Utility.DataAccess.ExecuteScalar(rexConn, rexTrans,
                        "spIRBStatusUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nWorkStatusID", SqlDbType.Int, RexIRBStatusId,
                        "@nTabTypeID", SqlDbType.Int, 1,
                        "@sTabType", SqlDbType.VarChar, Humans.Constants.RexIRBStatusType.InitialReview,
                        "@nIRBProtocolId", SqlDbType.Int, rexProtId)));

                    /// SET THE REX IRBStatusID on it.
                    if (irbStatusId != 0)
                        this.RexIRBStatusId = irbStatusId;

                    // now set the ProcessID on the IRBStatus record, rather than passing it 
                    // in as a new parameter to Rex, which would require MAJOR VB code changes to 
                    // Rex when cloning protocols. 
                    //UpdateIRBStatusForProcess
                    Utility.DataAccess.ExecuteNonQuery(rexConn, rexTrans,
                        "UpdateIRBStatusForProcess",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@tblIRBStatusId", SqlDbType.Int, RexIRBStatusId,
                        "@processId", SqlDbType.Int, Id));


                    if (insertStatusRecords)
                    {

                        // spIRBBoardStatusUpdate
                        int irbBoardStatusUpdateId = Convert.ToInt32(Utility.DataAccess.ExecuteScalar(rexConn, rexTrans,
                            "spIRBBoardStatusUpdate",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@nStatusId", SqlDbType.Int, RexIRBStatusId,
                            "@sTabType", SqlDbType.VarChar, Humans.Constants.RexIRBStatusType.InitialReview,
                            "@nIRBProtocolId", SqlDbType.Int, rexProtId,
                            "@nMailGroupId", SqlDbType.Int, 99,
                            "@bReviewers", SqlDbType.Int, 0)));

                        // spIRBBoardStatusDetailsUpdate
                        int irbBoardStatusDetailsId = Convert.ToInt32(Utility.DataAccess.ExecuteScalar(rexConn, rexTrans,
                            "spIRBBoardStatusDetailsUpdate",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@nIRBProtocolId", SqlDbType.Int, rexProtId,
                            "@nDetailID", SqlDbType.Int, irbBoardStatusUpdateId,
                            "@nStatusId", SqlDbType.Int, RexIRBStatusId,
                            "@nMailGroupId", SqlDbType.Int, 99,
                            "@bCurrentStatus", SqlDbType.Int, 1,
                            "@sStatus", SqlDbType.VarChar, Humans.Constants.ProtocolOverallStatusText.Submitted,
                            "@dtStatusModified", SqlDbType.DateTime, DateTime.Now)));
                    }

                    rexTrans.Commit();
                    insightTrans.Commit();
                }
                catch (Exception ex)
                {
                    Utility.ExceptionReport.WriteToEventLog("Moving data to Rex failed.", ex, true, PHSRAG.Utility.ExceptionReport.ReportType.Error);

                    if (rexTrans != null || rexConn.State != ConnectionState.Closed)
                        rexTrans.Rollback();
                    if (insightTrans != null || insightConn.State != ConnectionState.Closed)
                        insightTrans.Rollback();

                    throw ex;
                }
                finally
                {
                    if (rexConn.State != ConnectionState.Closed)
                        rexConn.Close();
                    if (insightConn.State != ConnectionState.Closed)
                        insightConn.Close();
                }


                // Lock the protocol down.
                Lock(insightConnectionString, cachePolicy, parentProt);

                // now update teh Rex database, doing all transactions within a Txn.

                SqlTransaction rexDataTrans = null;

                try
                {
                    rexConn.Open();
                    rexDataTrans = rexConn.BeginTransaction();

                    UpdateRexData(insightConnectionString, rexConnectionString, parentProt, rexParameters, rexDataTrans);
                    rexDataTrans.Commit();
                }
                catch (Exception ex)
                {
                    Utility.ExceptionReport.WriteToEventLog("Updating Rex data failed.", ex, true, PHSRAG.Utility.ExceptionReport.ReportType.Error);
                    if (rexTrans != null && rexTrans.Connection != null)
                        rexTrans.Rollback();
                    throw;
                }
                finally
                {
                    if (rexConn.State != ConnectionState.Closed)
                        rexConn.Close();
                }
            }
            #endregion
            #region Animals Submission
            else if (parentProt.ModuleType == Humans.Constants.ModuleType.Animals)
            {
                try
                {
                    rexParameters = (AnimalsRexParams)rexParameters;

                    rexConn.Open();
                    rexTrans = rexConn.BeginTransaction();

                    insightConn.Open();
                    insightTrans = insightConn.BeginTransaction();

                    bool newProtocol = false;
                    if (parentProt.ProtocolNumber == "" || parentProt.ProtocolNumber == string.Empty)
                    {
                        // first get the new Foldernumber. Note: set it to spaces so it will return all the characters.
                        // otherwise, if you pass in an emptystring, SQLServer defaults it to char(1), and you only get back the 
                        // first number from the query.
                        string protocolNumber = "                 ";

                        SqlParameter[] args = Utility.DataAccess.BuildSqlParameterArray(
                            "@sKeyName", SqlDbType.VarChar, "sFolderNumber",
                            "sKeyValue", SqlDbType.VarChar, protocolNumber);

                        int argCt = args.Length;
                        SqlParameter param = args[argCt - 1];
                        param.Direction = ParameterDirection.InputOutput;

                        DataSet rexResults = Utility.DataAccess.GetDataSet(rexConnectionString,
                            "spAnimalKeyGetNext", args);
                        protocolNumber = (string)param.Value;
                        parentProt.ProtocolNumber =
                            protocolNumber.Substring(0, 4) + "N" + protocolNumber.Substring(4, 6);

                        //insertStatusRecords = true;
                        newProtocol = true;
                    }

                    CDS.Person PI = new CDS.Person(cachePolicy);
                    PI.Load(insightConnectionString, parentProt.PIPersonId);

                    // check the PI has a Recom ID since Rex requires it.
                    if (PI != null && PI.RexRecomId == 0)
                    {
                        // insert the PI
                        PI.CreateRecomRecord(insightConnectionString, rexConnectionString);
                    }

                    CDS.Person iacucAdmin = new CDS.Person(cachePolicy);
                    iacucAdmin.Load(insightConnectionString, parentProt.AdministratorId);
                    if (iacucAdmin != null && iacucAdmin.RexRecomId == 0)
                    {
                        //insert
                        iacucAdmin.CreateRecomRecord(insightConnectionString, rexConnectionString);
                    }

                    CDS.Organization PIOrg = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrg.Load(insightConnectionString, PI.OrganizationId);

                    // create Institution, Department and Unit Orgs for the PI's Org.
                    CDS.Organization PIOrgInstitution = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgInstitution.Load(insightConnectionString, PIOrg.InstitutionId);

                    CDS.Organization PIOrgDept = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgDept.Load(insightConnectionString, PIOrg.DepartmentId);
                    CDS.Organization PIOrgUnit = new PHSRAG.Insight21.CDS.Organization(cachePolicy);
                    PIOrgUnit.Load(insightConnectionString, PIOrg.UnitId);



                    string unitName;
                    if (PIOrgUnit == null || PIOrgUnit.Name == null || PIOrgUnit.Name == string.Empty)
                        unitName = "(None)";
                    else
                        unitName = PIOrgUnit.Name + " " + PIOrgUnit.ChiefCode;

                    Sponsor sponsor = new Sponsor(cachePolicy);
                    try
                    {
                        sponsor.Load(insightConnectionString, parentProt.SponsorId);
                    }
                    catch { }

                    int currentDocumentNumber = 1;

                    // if the ID isn't zero, look it up to see if
                    // what we have is the current attachmentTitle # (in case it's
                    // been cloned in Rex bc of Amendment or Correction)

                    //TODO: Add new ones for Animals...
                    if (parentProt.RexProtocolId != 0)
                    {
                        currentDocumentNumber = base.GetCurentRexDocumentNumber(rexConnectionString, rexTrans, parentProt.ProtocolNumber, Constants.ModuleType.Animals);
                        parentProt.RexProtocolId = base.GetCurrentRexProtocolId(rexConnectionString, rexTrans, parentProt.ProtocolNumber, Constants.ModuleType.Animals);
                    }

                    // default these to Now. If it's not a new protocol, we'll look them up and 
                    // use the existing ones. Otherwise, we'll keep them as now for new ones.
                    DateTime dateReceived = DateTime.Now;
                    DateTime dateIssued = DateTime.Now;
                    DateTime dtApproval = DateTime.MinValue;
                    DateTime dtExpiration = DateTime.MinValue;
                    string sLegacyIdentifier = string.Empty;
                    string sOverallStatus = parentProt.OverallStatus;

                    // look up Ref Code for Species and type
                    System.Web.HttpApplicationState app = System.Web.HttpContext.Current.Application;
                    ICachePolicy appCcachePolicy = (ICachePolicy)app[Policy.Names.ApplicationCachePolicyKey];
                    CDS.RefCodes refCodes = new PHSRAG.Insight21.CDS.RefCodes(cachePolicy);
                    refCodes.Load(insightConnectionString, null);
                    
                    CDS.RefCode rexSpeciesRefCode = refCodes.GetRefCode(parentProt.SpeciesCode);
                    
                    CDS.RefCode rexProtocolType = refCodes.GetRefCode(parentProt.ProtocolTypeCode);

                    if (!newProtocol)
                    {

                        // get the existing date Received and Date Issued, and pass them in so as to not
                        // overwrite what Rex has. CDC: This will go away when these properties are added
                        // directly to the protocol in 2.1.4 (hopefully)....
                        DataSet protocolInfo = Utility.DataAccess.GetDataSet(
                            rexConn, rexTrans,
                            "spAnimalProtocolGet",
                            Utility.DataAccess.BuildSqlParameterArray("@nAnimalProtId", SqlDbType.Int, parentProt.RexProtocolId));


                        object o = protocolInfo.Tables[0].Rows[0]["dtReceived"];
                        dateReceived = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtAnimalApproval"];
                        dtApproval = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["dtAnimalExpiration"];
                        dtExpiration = (o == DBNull.Value) ? DateTime.MinValue : (DateTime)o;

                        o = protocolInfo.Tables[0].Rows[0]["sOverallStatus"];
                        if (o != DBNull.Value)
                        {
                            if ((string)o != sOverallStatus)
                                sOverallStatus = (string)o;
                        }
                    }

					foreach (AnimalsRexParams.HazardousAgent hazAgent in (rexParameters as AnimalsRexParams).HazardousAgents)
					{
						if (hazAgent.HazardType == "Rad")
							parentProt.RadioIsotope = true;
						else if (hazAgent.HazardType == "Chem")
							parentProt.ToxicChem = true;
						else if (hazAgent.HazardType == "Bio")
							parentProt.BiologicalAgent = true;
					}
                    DataSet rexReturnVals = Utility.DataAccess.GetDataSet(rexConn, rexTrans,
                       "[dbo].[spAnimalProtocolUpdate]",
                       Utility.DataAccess.BuildSqlParameterArray(
                       "@sAnimalShortTitle", SqlDbType.VarChar, parentProt.Title,
                       "@nAnimalProtID", SqlDbType.Int, (parentProt.RexProtocolId == 0) ? (object)System.DBNull.Value : (object)parentProt.RexProtocolId,
                       "@sAnimalTitle", SqlDbType.VarChar, parentProt.Title,
                       "@nDocumentNumber", SqlDbType.Int, currentDocumentNumber,
                       "@sFolderNumber", SqlDbType.VarChar, parentProt.ProtocolNumber,
                       "@nSponsorID", SqlDbType.Int, (sponsor.RexSponsorId == 0) ? (object)System.DBNull.Value : (object)sponsor.RexSponsorId,
                       "@nPIID", SqlDbType.Int, PI.RexRecomId,
                       "@nAdministratorID", SqlDbType.Int, iacucAdmin.RexRecomId,
                       "@sPrevFolderNumber", SqlDbType.VarChar, (rexParameters as AnimalsRexParams ).PreviousFolderNumber,
                       "@sLegacyIdentifier", SqlDbType.VarChar, (sLegacyIdentifier == string.Empty) ? (object)DBNull.Value : sLegacyIdentifier,
                       "@sPIInstitution", SqlDbType.VarChar, PIOrgInstitution.Name + " " + PIOrgInstitution.ChiefCode,
                       "@sPIDept", SqlDbType.VarChar, PIOrgDept.Name + " " + PIOrgDept.ChiefCode,
                       "@sPIUnit", SqlDbType.VarChar, unitName,
                       "@sPILab", SqlDbType.VarChar, "",
                       "@sProtocolType", SqlDbType.VarChar, rexProtocolType.Meaning,
                       "@dtReceived", SqlDbType.DateTime, dateReceived,
                       "@sOverallStatus", SqlDbType.VarChar, parentProt.OverallStatus,
                       "@sSpecies", SqlDbType.VarChar, rexSpeciesRefCode["REXMEANING"],
                       "@nTotalAnimals", SqlDbType.Int, parentProt.NumberOfAnimals,
                       "@dtAnimalStart", SqlDbType.DateTime, (parentProt.ProtocolPeriodStartDate == DateTime.MinValue) ? (object)DBNull.Value : parentProt.ProtocolPeriodStartDate,
                       "@dtAnimalEnd", SqlDbType.DateTime, (parentProt.ProtocolPeriodEndDate == DateTime.MinValue) ? (object)DBNull.Value : parentProt.ProtocolPeriodEndDate,
                       "@dtAnimalApproval", SqlDbType.DateTime, (dtApproval == DateTime.MinValue) ? (object)DBNull.Value : dtApproval,
                       "@dtAnimalExpiration", SqlDbType.DateTime, (dtExpiration == DateTime.MinValue) ? (object)DBNull.Value : dtExpiration,
					   "@bRadioisotope", SqlDbType.Bit, parentProt.RadioIsotope,
					   "@bBiologicalAgent", SqlDbType.Bit, parentProt.BiologicalAgent,
					   "@bToxicChem", SqlDbType.Bit, parentProt.ToxicChem,
                       "@bTissueTest", SqlDbType.Bit, parentProt.TissueTest,
                       "@bStudyStaff", SqlDbType.Bit, true,
                       "@bHousingOver12", SqlDbType.Bit, (rexParameters as AnimalsRexParams).HousingOver12,
                       "@bFedFund", SqlDbType.Bit, (rexParameters as AnimalsRexParams).FedFund,
                       "@bResubmission", SqlDbType.Bit, (rexParameters as AnimalsRexParams).Resubmission,
                       "bSpecialConsid", SqlDbType.Bit, (rexParameters as AnimalsRexParams).SpecialConsideration,
                       "@bCurrentStatus", SqlDbType.Bit, true
                       ));

                    // only update these if it's WIP (to prevent changing the status if the protocol is unlocked
                    // then locked again.
                    if (parentProt.OverallStatusId == (int)Humans.Constants.ProtocolOverallStatus.WorkInProgress)
                    {
                        parentProt.OverallStatusId = (int)Humans.Constants.ProtocolOverallStatus.Pending;
                        Status = Humans.Constants.ProtocolProcessStatus.Pending;
                        parentProt.SubmissionDate = DateTime.Now;
                        this.ReceivedDate = DateTime.Now;
                    }

                    if (parentProt.OverallStatusId == (int)Humans.Constants.ProtocolOverallStatus.Submitted)
                    {
                        this.ReceivedDate = DateTime.Now;
                    }

                    if (DateTime.MinValue == parentProt.SubmissionDate)
                        parentProt.SubmissionDate = DateTime.Now;

                    // 8055 set the date received to now if it's not populated.
                    if (this.ReceivedDate == DateTime.MinValue)
                    {
                        this.ReceivedDate = dateReceived;
                    }

                    parentProt.RexProtocolId = (int)rexReturnVals.Tables[0].Rows[0]["IdentityColumn"];

                    foreach (StaffPerson person in StudyStaff)
                    {
                        person.ProtocolNumber = parentProt.ProtocolNumber;
                    }

                    parentProt.Save(insightTrans);
                    Save(insightTrans);

                    UpdateRexAnimalData(insightConnectionString, 
                                        rexConnectionString, 
                                        parentProt, 
                                        rexParameters, 
                                        rexTrans,
                                        refCodes );

                    rexTrans.Commit();
                    insightTrans.Commit();
                }
                catch (Exception ex)
                {
                    Utility.ExceptionReport.WriteToEventLog("Moving data to Rex failed.", ex, true, PHSRAG.Utility.ExceptionReport.ReportType.Error);

                    if (rexTrans != null || rexConn.State != ConnectionState.Closed)
                        rexTrans.Rollback();
                    if (insightTrans != null || insightConn.State != ConnectionState.Closed)
                        insightTrans.Rollback();

                    throw ex;
                }
                finally
                {
                    if (rexConn.State != ConnectionState.Closed)
                        rexConn.Close();
                    if (insightConn.State != ConnectionState.Closed)
                        insightConn.Close();
                }


                // Lock the protocol down.
                Lock(insightConnectionString, cachePolicy, parentProt);
            }
            #endregion

            // NOTE: Don't call the base submit method for InitialReviews
            //base.Submit ( cachePolicy, insightConnectionString, rexConnectionString, executingAssemblyPath );
        }

        public override void Approve(SqlTransaction insightTransaction, SqlTransaction rexTtransaction,
               string insightConnectionString, string rexConnectionString, Protocol parentProtocol)
        {
            base.Approve(insightTransaction, rexTtransaction,
                         insightConnectionString, rexConnectionString, parentProtocol);

            RunApprovalScript();

            // set the latest Version to APPROVED!
            if (this.ProtocolVersions.Count > 0)
            {
                ProtocolVersion irVersion = (ProtocolVersion)this.ProtocolVersions[this.ProtocolVersions.Count - 1];

                irVersion.Status = Humans.Constants.ProtocolProcessStatus.ApprovedInitialApp;
                irVersion.Save(insightTransaction);
            }
           
        }
        #endregion


        #region Methods

        public ProtocolVersion GetInitiallyApprovedVersion()
        {
            ProtocolVersion returnProtocolVersion = null;

            foreach (ProtocolVersion protocolVersion in ProtocolVersions)
            {
                if (ProtocolVersions.Count == 1)
                    return protocolVersion;

                if (Constants.ProtocolProcessStatus.ApprovedInitialApp == protocolVersion.Status)
                {
                    returnProtocolVersion = protocolVersion;
                }
            }

            return returnProtocolVersion;

        }

        public ProtocolVersion GetLatestApprovedVersion()
        {
            ProtocolVersion returnProtocolVersion = null;

            foreach (ProtocolVersion protocolVersion in ProtocolVersions)
            {
                if (Constants.ProtocolProcessStatus.Merged == protocolVersion.Status)
                {
                    continue;
                }

                if (null == returnProtocolVersion ||
                    protocolVersion.Version > returnProtocolVersion.Version)
                {
                    returnProtocolVersion = protocolVersion;
                }
            }

            return returnProtocolVersion;
        }

        private void UpdateRexAnimalData(string insightConnectionString, string rexConnectionString, Protocol protocol,
            RexParams rexParameters, SqlTransaction rexTransaction, RefCodes refCodes )
        {
            #region staff updates.
            // Rex removes the staff to do its updates, so remove the staff first, then re-add them all.
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalStudyStaffDelete",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));

            // now move the study staff over to Rex, if they're not already there.
            for (int i = 0; i < StudyStaff.Count; ++i)
            {
                StaffPerson staff = (StaffPerson)StudyStaff[i];

				if (staff.IsCurrent)
				{
					if (staff.Person.RexRecomId == 0)
					{
						staff.Person.CreateRecomRecord(insightConnectionString,
							rexConnectionString);
					}

					int rexStudyStaffId =
						Convert.ToInt32(Utility.DataAccess.ExecuteScalar(
						rexTransaction.Connection,
						rexTransaction,
						"spAnimalStudyStaffUpdate",
						Utility.DataAccess.BuildSqlParameterArray(
						"@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
						"@nRecomAbstractID", SqlDbType.Int, staff.Person.RexRecomId,
						"@sPosition", SqlDbType.VarChar, staff.Role,
						"@bGetsMail", SqlDbType.Bit, staff.IsContactPerson,
                        "@sEmailAddress", SqlDbType.VarChar, staff.Person.Email
						)));
				}
            }
            #endregion


            #region Animal Species
            //Inorder to add the categories to the existing values, remove the delete step and add the current values added through amendment(done in SProc).
            //// delete any existing species info and then re-add them
            //Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
            //    "spAnimalSpeciesDelete",
            //    Utility.DataAccess.BuildSqlParameterArray(
            //    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId ));

            CDS.RefCode rexSpeciesRefCode = refCodes.GetRefCode(protocol.SpeciesCode);

            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalSpeciesUpdateFromInsight",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                "@sSpecies", SqlDbType.VarChar, rexSpeciesRefCode["REXMEANING"],
                "@nTotalAnimals", SqlDbType.Int, protocol.NumberOfAnimals,
                "@nAdult", SqlDbType.Int, (rexParameters as AnimalsRexParams).CatBNum,
                "@nFemaleLitter", SqlDbType.Int, (rexParameters as AnimalsRexParams).CatCNum,
                "@nGenerated", SqlDbType.Int, (rexParameters as AnimalsRexParams).CatDNum,
                "@sOther", SqlDbType.VarChar, (rexParameters as AnimalsRexParams).CatENum.ToString(),
                "@bHousingOver12", SqlDbType.Bit, (rexParameters as AnimalsRexParams).HousingOver12,
                "@bIsAmendment", SqlDbType.Bit, (rexParameters as AnimalsRexParams).IsAmendment));
            #endregion

            #region Housing
             // delete any existing species info and then re-add them
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalHousingDelete",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId ));


            for (int i = 0; i < (rexParameters as AnimalsRexParams).HousingLocation.Count; i++)
            {
                string housingLocation = (string)(rexParameters as AnimalsRexParams).HousingLocation[i];

                string housingLocationOther = string.Empty;
                if (housingLocation == "(Other)")
                    housingLocationOther = (rexParameters as AnimalsRexParams).HousingLocationOther;

                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spAnimalHousingUpdate",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                    "@nHousingId", SqlDbType.Int, 0, //// ?
                    "@sHousingLocation", SqlDbType.VarChar, housingLocation,
                    "@sDescription", SqlDbType.VarChar, housingLocationOther));
            }
            #endregion

            #region Special Handling
            // delete any existing species info and then re-add them
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalSpecialHandlingDelete",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));


            for (int i = 0; i < (rexParameters as AnimalsRexParams).SpecialHandling.Count; i++)
            {
                string specialHandling = (string)(rexParameters as AnimalsRexParams).SpecialHandling[i];
                            
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spAnimalSpecialHandlingUpdate",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                    "@nAnimalSpecialCharID", SqlDbType.Int, 0, //// ?
                    "@sSpecialCharacteristics", SqlDbType.VarChar, specialHandling));
            }
            #endregion

            #region Considerations
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalSpConsiderDelete",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));


            for (int i = 0; i < (rexParameters as AnimalsRexParams).Considerations.Count; i++)
            {
                string consideration = (string)(rexParameters as AnimalsRexParams).Considerations[i];

                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spAnimalSpConsiderUpdate",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                    "@nAnimalSpConsiderID", SqlDbType.Int, 0,
                    "@bApplies", SqlDbType.Bit, true,
                    "@sSpConsider", SqlDbType.VarChar, consideration));
            }
            #endregion

            #region Hazardous Agents
            if ((rexParameters as AnimalsRexParams).HazardousAgents.Count > 0)
            {
                for (int i = 0; i < (rexParameters as AnimalsRexParams).HazardousAgents.Count; i++)
                {
                    Humans.AnimalsRexParams.HazardousAgent hazAgent = (Humans.AnimalsRexParams.HazardousAgent)(rexParameters as AnimalsRexParams).HazardousAgents[i];

                    string hazardAgentType;
                    string hazardName;

                    if (hazAgent.HazardType == "Rad")
                    {
                        hazardAgentType = "Radioisotope";
                        // look up the ref code
                        CDS.RefCode isotope = refCodes.GetRefCode(hazAgent.Isotope);
                        hazardName = isotope.Meaning;
                    }
                    else if (hazAgent.HazardType == "Chem")
                    {
                        hazardAgentType = "Toxic Chemical/Carcinogen";
                        // look up the ref code
                        CDS.RefCode chemAgent = refCodes.GetRefCode(hazAgent.Chemical);
                        hazardName = chemAgent.Meaning;
                    }
                    else if (hazAgent.HazardType == "Bio")
                    {
                        hazardAgentType = "Biological Agent";
                        // look up the ref code
                        CDS.RefCode biologicalAgent = refCodes.GetRefCode(hazAgent.BioAgent);
                        hazardName = biologicalAgent.Meaning;
                    }
                    else
                    {
                        hazardAgentType = "Radioisotope";
                        hazardName = hazAgent.Isotope;    
                    }
                    // the stored proc parameters in rex that take hazard name limit to 50 chars
                    if (hazardName.Length > 50)
                        hazardName = hazardName.Substring(0, 50);

                    int detailID = 0;
                    DataSet hazardAgs = Utility.DataAccess.GetDataSet(rexTransaction.Connection, rexTransaction,
                        "spAnimalHazardGet",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sHazardAgentType", SqlDbType.VarChar, hazardAgentType));


                    // loop through each row and call delete on each detail.
                    if (hazardAgs.Tables[0] != null && hazardAgs.Tables[0].Rows.Count > 0)
                    {
                        for (int q = 0; q < hazardAgs.Tables[0].Rows.Count; q++)
                        {
                            detailID = (int)hazardAgs.Tables[0].Rows[q]["nAnimalProtHazardID"];
                        }
                    }
               

                    // lookup the AnimalHazardAgent ID
                    DataSet hazardIDs = Utility.DataAccess.GetDataSet(rexTransaction.Connection, rexTransaction,
                        "spAnimalHazardAgentIDGet",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@sAgentName", SqlDbType.VarChar, hazardName));

                    int hazAgentID = 0;
                    if (hazardIDs.Tables[0].Rows.Count > 0)
                    {   
                        for (int y = 0; y < hazardIDs.Tables[0].Rows.Count; y++)
                        {
                            hazAgentID = (int)hazardIDs.Tables[0].Rows[y]["nHazardAgentId"];
                        }
                    }

                    string building = string.Empty; 

                    if ( hazAgent.Building.Count > 0 )
                        building = (string)hazAgent.Building[0];
                        
                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spAnimalHazardUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtHazardID", SqlDbType.Int, detailID,
                        "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sHazardAgentType", SqlDbType.VarChar, hazardAgentType,
                        "@sHazardAgentName", SqlDbType.VarChar, hazardName, 
                        "@nHazardAgentID", SqlDbType.Int, hazAgentID, 
                        "@sRadioMicroCiPerAnimal", SqlDbType.VarChar, hazAgent.Dose, 
                        "@sRadioisotopePermit", SqlDbType.VarChar, hazAgent.PermitNum,
                        "@sRadioisotopePermitHolder", SqlDbType.VarChar, hazAgent.PermitName,
                        "@sDose", SqlDbType.VarChar, hazAgent.Dose,
                        "@sFrequency", SqlDbType.VarChar, hazAgent.Frequency,
                        "@sTotalDoses", SqlDbType.VarChar, hazAgent.TotalDoses,
                        "@sMethod", SqlDbType.VarChar, hazAgent.Route,
                        "@sDuration", SqlDbType.VarChar, hazAgent.Duration,
                        "@sBuilding", SqlDbType.VarChar, building, 
                        "@sRoom", SqlDbType.VarChar, hazAgent.Room,
                        "@sManipulations", SqlDbType.VarChar, hazAgent.Manipulations,
                        "@sSafetyComments", SqlDbType.VarChar, DBNull.Value ));    
                
                    // set the default equipment
                    //Utility.DataAccess.ExecuteNonQuery(rexTransaction.Commit, rexTransaction,
                    //    "spAnimalHazardDefaultEquipmGet",
                    //    Utility.DataAccess.BuildSqlParameterArray(
                    //        "", SqlDbType.Int, hazAgentID));
                 }
            }
            #endregion

            #region Procedures
            if ((rexParameters as AnimalsRexParams).Procedures.Count > 0)
            {
                //first delete any existing procedures
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                            "spAnimalProcedureDelete",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));


                // for each procedure, lookup the Pain type.
                for (int e = 0; e < (rexParameters as AnimalsRexParams).Procedures.Count; e++)
                {

                    Humans.AnimalsRexParams.Procedure procedure = (Humans.AnimalsRexParams.Procedure)(rexParameters as AnimalsRexParams).Procedures[e];

                    // Look-up the Pain category 
                    if (procedure.SurgName != null)
                    {
                        DataSet painType = Utility.DataAccess.GetDataSet(rexTransaction.Connection, rexTransaction,
                            "spAnimalSurgicalPainTypeGet",
                             Utility.DataAccess.BuildSqlParameterArray(
                            "@sProcedure", SqlDbType.VarChar, procedure.SurgName));

                        if (painType.Tables[0].Rows.Count > 0)
                        {
                            for (int z = 0; z < painType.Tables[0].Rows.Count; z++)
                            {
                                procedure.PainCat = (string)painType.Tables[0].Rows[z]["sValue3"];
                            }
                        }
                    }



                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spAnimalProcedureUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nProcedureID", SqlDbType.Int, 0,
                        "@sProcedure", SqlDbType.VarChar, procedure.SurgName,
                        "@sSurgicalType", SqlDbType.VarChar, procedure.SurgicalType,
                        "@sPain", SqlDbType.VarChar, procedure.PainCat,
                        "@sSurvivalType", SqlDbType.VarChar, procedure.Survival,
                        "@sBuildingCode", SqlDbType.VarChar, procedure.Building,
                        "@sRoom", SqlDbType.VarChar, procedure.Room));
                }

            }

            #endregion

            #region LabLocation
            if ((rexParameters as AnimalsRexParams).LabLocations.Count > 0)
            {
                //first delete any existing procedures
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                            "spAnimalLabDelete",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));

                int locationCount = (rexParameters as AnimalsRexParams).LabLocations.Count;
                // for each procedure, lookup the Pain type.
                for (int e = 0; e < locationCount; e++)
                {
                    Humans.AnimalsRexParams.LabLocation labLocation = (Humans.AnimalsRexParams.LabLocation)(rexParameters as AnimalsRexParams).LabLocations[e];

                    // Look-up the lab
                    if (!string.IsNullOrEmpty(labLocation.BuildingCode))
                    {
                        Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spAnimalLabUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nLabID", SqlDbType.Int, 0,
                        "@sBuildingCode", SqlDbType.VarChar, labLocation.BuildingCode,
                        "@sRoom", SqlDbType.VarChar, labLocation.RoomNumber));
                    }
                                       
                }

            }

            #endregion
            
            #region Update Tumor Cell Lines
            if ((rexParameters as AnimalsRexParams).TumorCellLines.Count > 0)
            {
                // Delete any children
                DataSet TissueResults = Utility.DataAccess.GetDataSet(rexTransaction.Connection, rexTransaction,
                    "spAnimalTissueHeaderGet",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));

                // loop through each row and call delete on each detail.
                if (TissueResults.Tables[0] != null && TissueResults.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < TissueResults.Tables[0].Rows.Count; i++)
                    {
                        int detailID = (int)TissueResults.Tables[0].Rows[i]["nTissueId"];
                        
                        Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                            "spAnimalTissueDetailDelete",
                            Utility.DataAccess.BuildSqlParameterArray(
                            "@nTissueId", SqlDbType.Int, detailID));
                    }

                }

                // delete all drugs for this protocol, then re-add them.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spAnimalTissueHeaderDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));

                // Delete and re-add tumor cell lines
                for (int i = 0; i < (rexParameters as AnimalsRexParams).TumorCellLines.Count; i++)
                {
                    Humans.AnimalsRexParams.TumorCellLine tumorCellLine = (Humans.AnimalsRexParams.TumorCellLine)(rexParameters as AnimalsRexParams).TumorCellLines[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spAnimalTissueHeaderUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nTissueID", SqlDbType.Int, 0,
                        "@nHeaderNO", SqlDbType.Int, i,
                        "@sCellLine", SqlDbType.VarChar, tumorCellLine.TumorCellLineName,
                        "@sCellLineOrigin", SqlDbType.VarChar, tumorCellLine.RodentOrigin,
                        "@sOtherDescription", SqlDbType.VarChar, tumorCellLine.OriginOther ?? (object)DBNull.Value,
                        "@sCellLineSource", SqlDbType.VarChar, DBNull.Value,
                        "@bPassagedRodents", SqlDbType.Bit, tumorCellLine.PassRodent,
                        "@sFacilityPassaged", SqlDbType.VarChar, DBNull.Value));
                }
            }
            #endregion

            #region ProtocolAbstract
            // string together all of the Abstract answers

            StringBuilder sb = new StringBuilder();
            sb.Append((rexParameters as AnimalsRexParams).Objective);
            sb.Append(Environment.NewLine);
            sb.Append(Environment.NewLine);
            sb.Append((rexParameters as AnimalsRexParams).Significance);
            sb.Append(Environment.NewLine);
            sb.Append(Environment.NewLine);
            sb.Append((rexParameters as AnimalsRexParams).SimExp);
            sb.Append(Environment.NewLine);
            sb.Append(Environment.NewLine);
            sb.Append((rexParameters as AnimalsRexParams).Prior);
            sb.Append(Environment.NewLine);
            sb.Append(Environment.NewLine);
            sb.Append((rexParameters as AnimalsRexParams).Endpoints);

            string protocolAbstract =  sb.ToString();
            
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spAnimalProtocolAbstractUpdate",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
                "@sProtocolAbstract", SqlDbType.Text, protocolAbstract,
                "@sProtocolAbstractRTF", SqlDbType.Text, protocolAbstract));
            #endregion

			#region Tissues
			if ((rexParameters as AnimalsRexParams).Tissues.Count > 0)
			{
				//first delete any existing tissues
				Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
							"spAnimalTissueDelete",
							Utility.DataAccess.BuildSqlParameterArray(
							"@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId));

				//re-add them
				for (int e = 0; e < (rexParameters as AnimalsRexParams).Tissues.Count; e++)
				{

					Humans.AnimalsRexParams.Tissue tissue = (Humans.AnimalsRexParams.Tissue)(rexParameters as AnimalsRexParams).Tissues[e];

					Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
						"spAnimalTissueUpdate",
						Utility.DataAccess.BuildSqlParameterArray(
						"@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
						"@nTissueID", SqlDbType.Int, 0,
						"@sTissueType", SqlDbType.VarChar, tissue.Type,
						"@sTissueSource", SqlDbType.VarChar, tissue.Source,
						"@sProtocolContact", SqlDbType.VarChar, ((string.IsNullOrEmpty(tissue.ProtocolContact)) ? DBNull.Value : (object)tissue.ProtocolContact),
						"@sProtocolVendor", SqlDbType.VarChar, ((string.IsNullOrEmpty(tissue.VendorName)) ? DBNull.Value : (object)tissue.VendorName)));
				}

			}

			#endregion

			#region BoardReviews
			if ((rexParameters as AnimalsRexParams).BoardReviews.Count > 0)
			{
                // get existing board reviews
				DataSet boardReviewResults = Utility.DataAccess.GetDataSet(rexTransaction.Connection, rexTransaction,
					"spAnimalBoardReviewGet",
					Utility.DataAccess.BuildSqlParameterArray(
					"@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
					"@sDocumentType", SqlDbType.VarChar, "Initial Review"));

				for (int e = 0; e < (rexParameters as AnimalsRexParams).BoardReviews.Count; e++)
				{
					Humans.AnimalsRexParams.BoardReview boardReview = (Humans.AnimalsRexParams.BoardReview)(rexParameters as AnimalsRexParams).BoardReviews[e];

                    bool reviewExists = false;
                    if (boardReviewResults.Tables.Count > 0)
                    {
                        foreach (DataRow row in boardReviewResults.Tables[0].Rows)
                        {
                            if (Convert.ToInt32(row["nMailGroupID"]) == boardReview.MailGroupId)
                            {
                                reviewExists = true;
                            }
                        }
                    }

                    // Only add the board review if it doesn't already exist
                    if (reviewExists) continue;

					Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
						"spAnimalBoardReviewUpdate",
						Utility.DataAccess.BuildSqlParameterArray(
						"@nAnimalProtID", SqlDbType.Int, protocol.RexProtocolId,
						"@nBoardReviewID", SqlDbType.Int, 0,
						"@nPeriod", SqlDbType.Int, boardReview.Period,
						"@sDocumentType", SqlDbType.VarChar, boardReview.DocumentType,
						"@nMailGroupID", SqlDbType.Int, boardReview.MailGroupId,
						"@sReviewType", SqlDbType.VarChar, boardReview.ReviewType,
						"@nMeetingID", SqlDbType.Int, (boardReview.MeetingId == 0) ? DBNull.Value : (object)boardReview.MeetingId,
						"@dtStatus", SqlDbType.DateTime, DateTime.Now.ToShortDateString(),
						"@bReviewers", SqlDbType.Bit, boardReview.Reviewers,
						"@sBoardStatus", SqlDbType.VarChar, boardReview.BoardStatus,
						"@sOverallStatus", SqlDbType.VarChar, boardReview.OverallStatus,
						"@bCurrentStatus", SqlDbType.Bit, boardReview.CurrentStatus,
						"@sNote", SqlDbType.Text, boardReview.Note,
						"@sRTFNote", SqlDbType.Text, boardReview.RTFNote,
						"@sStaffNote", SqlDbType.Text, boardReview.StaffNote,
						"@sStaffNoteRTF", SqlDbType.Text, boardReview.StaffNoteRTF,
						"@dtNotified", SqlDbType.DateTime, (boardReview.DateNotified == DateTime.MinValue) ? DBNull.Value : (object)boardReview.DateNotified,
						"@dtResponded", SqlDbType.DateTime, (boardReview.DateResponded == DateTime.MinValue) ? DBNull.Value : (object)boardReview.DateResponded,
						"@bVetMonitor", SqlDbType.Bit, boardReview.VetMonitor));
				}

                // Notes...
                if ((rexParameters as AnimalsRexParams).AnnReviewNotes != "" &&
                    (rexParameters as AnimalsRexParams).AnnReviewNotes != null &&
                    (rexParameters as AnimalsRexParams).AnnReviewNotes != string.Empty)
                {
                    // spAnimalNoteUpdate
                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spAnimalNoteUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nAnimalProtId", SqlDbType.Int, protocol.RexProtocolId,
                        "@sNote", SqlDbType.Text, (rexParameters as AnimalsRexParams).AnnReviewNotes,
                        "@sRTFNote", SqlDbType.Text, (rexParameters as AnimalsRexParams).AnnReviewNotes));
                }
			}

			#endregion

        }

        /// <summary>
        /// the UpdateRexData method updates other protocol data in Rex for 
        /// Drugs, Devices, study population, and other fields.
        /// </summary>
        /// <param name="protocol">the protocol to be updated</param>
        /// <param name="rexParameters">an instance of the RexParams object containing data populated via script</param>
        /// <param name="rexTransaction">a SQL Transaction to ensure all updates are done within a transaction.</param>
        private void UpdateRexData(string insightConnectionString, string rexConnectionString, Protocol protocol, 
			RexParams rexParameters, SqlTransaction rexTransaction)
        {

            #region UpdateDrugs
            if (rexParameters.Drugs.Count > 0)
            {
                // delete all drugs for this protocol, then re-add them.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBDrugsBiologicDeviceDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sItemType", SqlDbType.VarChar, Drug));

                // Delete and re-add drugs, devices, etc.
                for (int i = 0; i < rexParameters.Drugs.Count; i++)
                {
                    Humans.RexParams.RexDrug rexDrug = (Humans.RexParams.RexDrug)rexParameters.Drugs[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBDrugsBiologicUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nItemCode", SqlDbType.Int, rexDrug.ExternalId,
                        "@sItemName", SqlDbType.VarChar, rexDrug.DrugName,
                        "@sItemType", SqlDbType.VarChar, Drug,
                        "@sGenericName", SqlDbType.VarChar, rexDrug.DrugName,
                        "@sPreGenericName", SqlDbType.VarChar, rexDrug.DrugName,
                        "@sFDAStatus", SqlDbType.VarChar, rexDrug.FDAStatus,
                        "@sDrugPhase", SqlDbType.VarChar, rexDrug.StudyPhase,
                        "@sINDIDENumber", SqlDbType.VarChar, rexDrug.IndNumber,
                        "@sINDIDEHolder", SqlDbType.VarChar, rexDrug.SponsorOfIND,
                        "@sDrugDispensing", SqlDbType.VarChar, rexDrug.Dispensing,
                        "@sDispensDescription", SqlDbType.VarChar, rexDrug.DispensingOther));
                }
            }
            #endregion

            #region UpdateDevices
            if (rexParameters.Devices.Count > 0)
            {
                // delete all drugs for this protocol, then re-add them.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBDrugsBiologicDeviceDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sItemType", SqlDbType.VarChar, Device));

                // Delete and re-add drugs, devices, etc.
                for (int i = 0; i < rexParameters.Devices.Count; i++)
                {
                    Humans.RexParams.RexDevice rexDevice = (Humans.RexParams.RexDevice)rexParameters.Devices[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBDeviceUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nItemCode", SqlDbType.Int, rexDevice.ExternalId,
                        "@sItemName", SqlDbType.VarChar, rexDevice.DeviceName,
                        "@sGenericName", SqlDbType.VarChar, rexDevice.DeviceName,
                        "@sDeviceType", SqlDbType.VarChar, string.Empty,
                        "@sFDAStatusDevice", SqlDbType.VarChar, rexDevice.FDAStatus,
                        "@sDeviceRisk", SqlDbType.VarChar, rexDevice.NonSigRisk,
                        "@sDeviceCategory", SqlDbType.VarChar, string.Empty,
                        "@sINDIDENumber", SqlDbType.VarChar, rexDevice.IdeNumber,
                        "@sINDIDEHolder", SqlDbType.VarChar, rexDevice.SponsorOfIDE));
                }
            }
            #endregion

            #region Performance Sites
            if (rexParameters.PartnersSites.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, EnrollmentSite));

                for (int i = 0; i < rexParameters.PartnersSites.Count; i++)
                {
                    string partnersSite = (string)rexParameters.PartnersSites[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, EnrollmentSite,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, partnersSite));
                }
            }
            #endregion

            #region Keywords
            if (rexParameters.Keywords.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBKeywordDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId));

                for (int i = 0; i < rexParameters.Keywords.Count; i++)
                {
                    string currStudyType = (string)rexParameters.Keywords[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, StudyType,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, currStudyType));
                }
            }
            #endregion

            #region Study Types
            if (rexParameters.StudyTypes.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, StudyType));

                for (int i = 0; i < rexParameters.StudyTypes.Count; i++)
                {
                    string currStudyType = (string)rexParameters.StudyTypes[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, StudyType,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, currStudyType));
                }
            }
            #endregion

            #region Subjects
            if (rexParameters.SubjectTypes.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, SubjectType));

                for (int i = 0; i < rexParameters.SubjectTypes.Count; i++)
                {
                    string currSubjectType = (string)rexParameters.SubjectTypes[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, SubjectType,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, currSubjectType));
                }
            }
            #endregion

            #region Remuneration
            if (rexParameters.Remunerations.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBRemunerationDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId));

                for (int i = 0; i < rexParameters.Remunerations.Count; i++)
                {
                    Humans.RexParams.RexRemuneration rexRemun =
                        (Humans.RexParams.RexRemuneration)rexParameters.Remunerations[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBRemunerationUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nRemunerationID", SqlDbType.Int, 0, ///????
                        "@sRemunerationType", SqlDbType.VarChar, rexRemun.Type,
                        "@cAmount", SqlDbType.Money, (rexRemun.Amount == string.Empty) ? 0 : (object)rexRemun.Amount,
                        "@sRemunerationDescription", SqlDbType.VarChar, rexRemun.OtherDescription));
                }
            }
            #endregion

            #region REcruitment
            if (rexParameters.Recruitment.Count > 0)
            {
                // delete all recruitment entries for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBRecruitmentProcDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId));

                for (int i = 0; i < rexParameters.Recruitment.Count; i++)
                {

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBRecruitmentProcUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@nRecruitmentID", SqlDbType.Int, 0,
                        "@sRecruitmentProcedure", SqlDbType.VarChar, (string)rexParameters.Recruitment[i],
                        "@nToBeUsed", SqlDbType.Int, 1));
                }

            }
            #endregion

            #region Consent
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spIRBConsentProcUpdate",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                "@bConsentWaived", SqlDbType.Bit, rexParameters.ConsentWaived,
                "@bPatientAdvocate", SqlDbType.Bit, rexParameters.PatientAdvocateUsed,
                "@bConsentWrittenWaived", SqlDbType.Bit, rexParameters.WrittenConsentWaived,
                "@bAthouriztnWaived", SqlDbType.Bit, rexParameters.AuthorizationWaived,
                "@bWrittenAthouriztnWaived", SqlDbType.Bit, rexParameters.WrittenAuthorizationWaived));
            #endregion

            #region Radiation
            if (rexParameters.Radiations.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, RadiationCategory));

                for (int i = 0; i < rexParameters.Radiations.Count; i++)
                {
                    string radiation = (string)rexParameters.Radiations[i];

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, RadiationCategory,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, radiation));
                }
            }
            #endregion

            #region MedRecords Human Material Sources
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spIRBMedRecordHumMatUpdate",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                "@bMedicalRecordsRequested", SqlDbType.Bit, rexParameters.MedicalRecords, // change to 1 or 0
                "@sStudyPopulationDescription", SqlDbType.VarChar, (rexParameters.StudyPopulationDescription == string.Empty) ? (object)DBNull.Value : (object)rexParameters.StudyPopulationDescription,
                "@bMedicalRecordsIdentifiers", SqlDbType.Bit, rexParameters.MedRecIdentifiers,
                "@bMedicalRecordsDisclose", SqlDbType.Bit, rexParameters.MedRecDisclose,
                "@dtSurveyStart", SqlDbType.DateTime, (rexParameters.HumMatSurveyStart == DateTime.MinValue) ? (object)DBNull.Value : (object)rexParameters.HumMatSurveyStart,
                "@dtSurveyEnd", SqlDbType.DateTime, (rexParameters.HumMatSurveyEnd == DateTime.MinValue) ? (object)DBNull.Value : (object)rexParameters.HumMatSurveyEnd,
                "@bHumanMaterialsRequested", SqlDbType.Bit, rexParameters.HumanMaterial,
                "@sHumanMaterialDescription", SqlDbType.VarChar, (rexParameters.HumMatDescription == string.Empty) ? (object)DBNull.Value : (object)rexParameters.HumMatDescription,
                "@bHumanMaterialIdentifiers", SqlDbType.Bit, rexParameters.HumMatIdentifiers));

            #endregion

            #region MedicalRecordsSources
            // Add these in here... MedicalRecordsSource
            if (rexParameters.MedicalRecordSources.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, MedicalRecordsSource));

                for (int i = 0; i < rexParameters.MedicalRecordSources.Count; i++)
                {
                    string source = (string)rexParameters.MedicalRecordSources[i];

                    string medSourceOther = string.Empty;
                    if (source == "Other")
                        medSourceOther = rexParameters.MedSourceOther;

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, MedicalRecordsSource,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, source,
                        "@sValue2", SqlDbType.VarChar, medSourceOther));
                }

            }
            #endregion

            #region SpecialConsideration
            if (rexParameters.SpecialConsiderations.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, SpecialConsideration));

                for (int i = 0; i < rexParameters.SpecialConsiderations.Count; i++)
                {
                    string specConsid = (string)rexParameters.SpecialConsiderations[i];

                    string specConsidOther = string.Empty;
                    if (specConsid == "Other")
                        specConsidOther = rexParameters.SpecConsidOther;

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, SpecialConsideration,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, specConsid,
                        "@sValue2", SqlDbType.VarChar, specConsidOther));
                }

            }
            #endregion


            #region HumanMaterial Sources
            if (rexParameters.HumanMaterialSources.Count > 0)
            {
                // delete all sitest for this protocol, then re-add the current ones.
                Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                    "spIRBMiscellaneousDelete",
                    Utility.DataAccess.BuildSqlParameterArray(
                    "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                    "@sFieldName", SqlDbType.VarChar, HumanMaterialSources));

                for (int i = 0; i < rexParameters.HumanMaterialSources.Count; i++)
                {
                    string source = (string)rexParameters.HumanMaterialSources[i];
                    string otherDescription = string.Empty;

                    if (source == "Other")
                        otherDescription = (string)rexParameters.HumMatOtherDescription;

                    Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                        "spIRBMiscellaneousUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId,
                        "@sFieldName", SqlDbType.VarChar, HumanMaterialSources,
                        "@nMiscID", SqlDbType.Int, 0,
                        "@sValue", SqlDbType.VarChar, source,
                        "@sValue2", SqlDbType.VarChar, otherDescription));
                }
            }
            #endregion

            #region staff updates.
            // Rex removes the staff to do its updates, so remove the staff first, then re-add them all.
            Utility.DataAccess.ExecuteNonQuery(rexTransaction.Connection, rexTransaction,
                "spIRBStudyStaffDelete",
                Utility.DataAccess.BuildSqlParameterArray(
                "@nIRBProtocolID", SqlDbType.Int, protocol.RexProtocolId));

            // now move the study staff over to Rex, if they're not already there.
            for (int i = 0; i < StudyStaff.Count; ++i)
            {
                StaffPerson staff = (StaffPerson)StudyStaff[i];

                // don't move over the PI.
                if (staff.RoleId != Humans.Constants.StaffRoleType.PrincipalInvestigator && staff.IsCurrent)
                {

                    if (staff.Person.RexRecomId == 0)
                    {
                        staff.Person.CreateRecomRecord(insightConnectionString,
                            rexConnectionString);
                    }

                    int rexStudyStaffId =
                        Convert.ToInt32(Utility.DataAccess.ExecuteScalar(
                        rexTransaction.Connection,
                        rexTransaction,
                        "spIRBStudyStaffUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolId", SqlDbType.Int, protocol.RexProtocolId,
                        "@nRecomAbstractID", SqlDbType.Int, staff.Person.RexRecomId,
                        "@sName", SqlDbType.VarChar, string.Empty, // not used, but Rex needs it...
                        "@sPosition", SqlDbType.VarChar, staff.RexRoleName,
                        "@bGetsMail", SqlDbType.Bit, staff.IsContactPerson,
                        "@sEmailAddress", SqlDbType.VarChar, staff.Person.Email
                        )));
                }
            }

            for (int i = 0; i < NonStudyStaff.Count; ++i)
            {
                NonStaffPerson nonStaff = (NonStaffPerson)NonStudyStaff[i];

                if (nonStaff.IsContactPerson)
                {                    
                    if (nonStaff.Person.RexRecomId == 0)
                    {
                        nonStaff.Person.CreateRecomRecord(insightConnectionString,
                            rexConnectionString);
                    }

                    int rexStudyStaffId =
                        Convert.ToInt32(Utility.DataAccess.ExecuteScalar(
                        rexTransaction.Connection,
                        rexTransaction,
                        "spIRBStudyStaffUpdate",
                        Utility.DataAccess.BuildSqlParameterArray(
                        "@nIRBProtocolId", SqlDbType.Int, protocol.RexProtocolId,
                        "@nRecomAbstractID", SqlDbType.Int, nonStaff.Person.RexRecomId,
                        "@sName", SqlDbType.VarChar, string.Empty, // not used, but Rex needs it...
                        "@sPosition", SqlDbType.VarChar, "M-Gets Mail",
                        "@bGetsMail", SqlDbType.Bit, nonStaff.IsContactPerson,
                        "@sEmailAddress", SqlDbType.VarChar, nonStaff.Person.Email
                        )));                    
                }
            }
           
            #endregion
        }
        #endregion
    }
}
